Re: [SQL] OUTER JOINs in PostgreSQL

At 23:47 +0300 on 02/06/1999, Ant9000 wrote:


> The best I was able to obtain is this:
>
> SELECT master.*,detail.lastvisit,detail.info FROM master,detail
> WHERE master.id=detail.id
> UNION
> SELECT *,NULL AS lastvisit,NULL AS info FROM master
> WHERE id NOT IN (SELECT id  FROM detail);
>
> which is (at best) unelegant; in MS Access you could do something like
>
> SELECT master.*,detail.lastvisit,detail.info FROM master LEFT JOIN detail
> ON master.id=detail.id;
>
> With Oracle, there's an even shorter solution:
>
> SELECT master.*,detail.lastvisit,detail.info FROM master,detail
> WHERE master.id=detail.id(+);
>
>
> Is anything like that available with PostgreSQL?

No, outer joins are not supported in PostgreSQL. Your solution is the
accepted workaround, although I tend to recommend WHERE NOT EXISTS ( select
* from detail WHERE detail.id = master.id )in the second clause - it is
more efficient.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma